﻿using System;
using System.Collections.Generic;
using System.Linq;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using System.Reflection;
using Microsoft.SqlServer.Server;
using Player.StoreProcedure;

namespace Player.Base
{
    //InputMappingAttribute
    //OutputMappingAttribute
    //VariableAttribute
    public abstract class StoredProcedure<T, TInput, TOutput, TVariables>
        where TInput : struct
        where TOutput : struct
        where TVariables : struct
    {
        protected static SqlParameter[] Parameters(TInput input)
        {
            Dictionary<int, SqlParameter> parameters = new Dictionary<int, SqlParameter>();

            // Add all the parameters from Input struct with the initialied values
            foreach (FieldInfo field in typeof(TInput).GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
            {
                InputMappingAttribute attr = Attribute.GetCustomAttribute(field, typeof(InputMappingAttribute)) as InputMappingAttribute;
                if (attr == null) throw new Exception("Fields in Input struct should have InputMapping attribute mapped to the variables");
                if (parameters.ContainsKey(attr.Variable)) throw new Exception(string.Format("Variable '{0}' is already mapped by another field", (TVariables)(Object)attr.Variable));

                object val = typeof(TInput).InvokeMember(field.Name,
                    BindingFlags.GetField | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic,
                    null, input, null);
                parameters[attr.Variable] = new SqlParameter("@" + (TVariables)(object)attr.Variable, val);

                // For parameter input value is null, we need to define the value type for the SqlParemeter, 
                // and change its value to DBNull.Value
                if (val == null)
                {
                    SetSqlDbTypeForNullValue(parameters[attr.Variable], field.FieldType);
                }
            }

            // Add all the parameters from Output class
            foreach (FieldInfo field in typeof(TOutput).GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
            {
                OutputMappingAttribute attr = Attribute.GetCustomAttribute(field, typeof(OutputMappingAttribute)) as OutputMappingAttribute;
                if (attr != null)
                {
                    if (parameters.ContainsKey(attr.Variable)) throw new Exception(string.Format("Variable '{0}' is already mapped by another field", (TVariables)(Object)attr.Variable));
                    SqlParameter parameter = new SqlParameter("@" + (TVariables)(object)attr.Variable, attr.DbType, attr.Size);

                    parameter.Direction = ParameterDirection.Output;
                    parameters[attr.Variable] = parameter;
                }
            }

            // Do the checking to make sure the defined variables are mapped by either Input class or Output class
            FieldInfo[] variables = typeof(TVariables).GetFields(BindingFlags.Public | BindingFlags.Static);
            foreach (FieldInfo variable in variables)
            {
                int key = (int)variable.GetRawConstantValue();
                if (!parameters.ContainsKey(key)) throw new Exception(string.Format("Variable '{0}' is not mapped", variable.Name));

                SqlParameter parameter = parameters[key];
                VariableAttribute attr = Attribute.GetCustomAttribute(variable, typeof(VariableAttribute)) as VariableAttribute;

                bool inputDirection = true; // No attribute means input direction
                if (attr != null && attr.Direction == Direction.Output) inputDirection = false;

                if (inputDirection && parameter.Direction == ParameterDirection.Output)
                {
                    throw new Exception(string.Format("Variable '{0}' is mapped to Output struct.  It should be mapped to Input struct", variable.Name));
                }
                else if (!inputDirection && parameter.Direction == ParameterDirection.Input)
                {
                    throw new Exception(string.Format("Variable '{0}' is mapped to Input struct.  It should be mapped to Output struct", variable.Name));
                }
            }

            List<SqlParameter> ret = new List<SqlParameter>();
            foreach (SqlParameter parameter in parameters.Values)
            {
                ret.Add(parameter);
            }

            return ret.ToArray();
        }

        protected static string Build(params object[] values)
        {
            string ret = SqlHelper.Build(values);
            return ret;
        }

        protected static string ToSql(params object[] values)
        {
            return SqlHelper.Build(values);
        }

        protected static string Convert(object type, object expression)
        {
            return SqlHelper.Convert(type, expression);
        }

        protected static string QuoteChar(string value)
        {
            return string.Format(CultureInfo.InvariantCulture, "'{0}'", value);
        }

        protected static string QuoteCharUnicode(string value)
        {
            return string.Format(CultureInfo.InvariantCulture, "N'{0}'", value);
        }

        protected static string DateTimeToBigInt(object expression)
        {
            return string.Format(CultureInfo.InvariantCulture, "CAST(CAST({0} AS FLOAT)*864000000000 + 599266080000000000 AS BIGINT)",
                                 Build(expression));
        }

        protected static string BigIntToDateTime(object expression)
        {
            // We have some round off issue here.  In order to make sure the reverse operation is not smaller than the original one, we need to add 33.34 offset
            return string.Format(CultureInfo.InvariantCulture, "CAST((CAST({0} - 599266080000000000 AS FLOAT) + 33.34)/864000000000 AS DATETIME)",
                                 Build(expression));
        }

        protected static int ColumnCount
        {
            get
            {
                int i = 0;
                foreach (FieldInfo fi in typeof(TOutput).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
                {
                    ColumnMappingAttribute attr = Attribute.GetCustomAttribute(fi, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
                    if (attr != null) i++;
                }

                return i;
            }
        }

        protected static string AllColumns
        {
            get
            {
                List<string> columns = new List<string>();
                foreach (FieldInfo fi in typeof(TOutput).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
                {
                    ColumnMappingAttribute attr = Attribute.GetCustomAttribute(fi, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
                    if (attr != null)
                    {
                        columns.Add(attr.Name);
                    }
                }

                return string.Join(", ", columns.ToArray());
            }
        }

        protected static string List(params object[] items)
        {
            return SqlHelper.List(items);
        }

        private static void SetSqlDbTypeForNullValue(SqlParameter parameter, Type valueType)
        {
            // System.ComponentModel.TypeConverter.ConvertFrom() is not used as it is forbidden in CLR Stored Proc
            SqlDbType resultDbType = parameter.SqlDbType;
            int resultSize = parameter.Size;
            object resultValue = parameter.Value;

            if (resultValue == null)
            {
                // Only handle varchar(max), varbinary(max) for which optional stored proc parameter is not supported
                if (valueType == Type.GetType("System.Byte[]"))
                {
                    resultDbType = SqlDbType.VarBinary;
                    resultSize = int.MaxValue;
                    resultValue = DBNull.Value;
                }
                else if (valueType == typeof(string))
                {
                    resultDbType = SqlDbType.VarChar;
                    resultSize = int.MaxValue;
                    resultValue = DBNull.Value;
                }

                parameter.SqlDbType = resultDbType;
                parameter.Size = resultSize;
                parameter.Value = resultValue;
            }
        }

        public static IEnumerable<TOutput> Execute(string connectionString, TInput input)
        {
            return Execute(connectionString, input, CommandBehavior.Default, -1);
        }

        public static IEnumerable<TOutput> Execute(string connectionString, TInput input, CommandBehavior behavior)
        {
            return Execute(connectionString, input, behavior, -1);
        }

        public static IEnumerable<TOutput> Execute(string connectionString, TInput input, CommandBehavior behavior, int commandTimeout)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                //Create Command: T => Storeprocedure name
                //                method parameters => StoreProcedure Parameters
                using (SqlCommand command = CreateCommand(connection, input))
                {
                    // Return all rows
                    SqlDataReader reader;
                    try
                    {
                        // Override command timeout
                        if (commandTimeout >= 0)
                        {
                            command.CommandTimeout = commandTimeout;
                        }

                        reader = command.ExecuteReader(behavior);
                    }
                    catch (SqlException e)
                    {
                        connection.Trace("Error - {0}", e.ToString());
                        throw;
                    }

                    int count = 0;
                    try
                    {
                        do
                        {
                            while (reader.Read())
                            {
                                count++;
                                yield return CreateColumnOutput(reader);
                            }
                        } while (reader.NextResult());
                    }
                    finally
                    {
                        command.Cancel();
                        reader.Close();
                    }

                    connection.Trace("Number of rows returned: {0}", count);

                    //It can this storeprocedure exists? return table and then return ouput??
                    // After reading all the data from DataReader, return the data for output parameters
                    List<object> ret = new List<object>();
                    foreach (SqlParameter parameter in command.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.Output)
                        {
                            ret.Add(parameter.Value);
                            connection.Trace("Return {0} = {1}", parameter.ParameterName, ToSqlString(parameter.Value));
                        }
                    }

                    if (ret.Count > 0)
                        yield return CreateVariableOutput(ret.ToArray());
                }
            }
        }

        public static TOutput ExecuteNonQuery(string connectionString, TInput input)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                return ExecuteNonQuery(connection, input);
            }
        }

        public static TOutput ExecuteNonQuery(SqlConnection connection, TInput input)
        {
            using (SqlCommand command = CreateCommand(connection, input))
            {
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    connection.Trace("Error - {0}", e.ToString());
                    throw;
                }

                List<object> ret = new List<object>();
                foreach (SqlParameter parameter in command.Parameters)
                {
                    if (parameter.Direction == ParameterDirection.Output)
                    {
                        ret.Add(parameter.Value);
                        connection.Trace("Return {0} = {1}", parameter.ParameterName, parameter.Value);
                    }
                }
                return CreateVariableOutput(ret.ToArray());
            }
        }

        /// <summary>
        /// Converts the multiple string values to single string input.
        /// </summary>
        /// <param name="values">The multiple string values.</param>
        /// <returns>The single string input.</returns>
        public static string ConvertToInput(IList<string> values)
        {
            return SqlHelper.MulitpleValueString.ConvertToInput(values);
        }

        /// <summary>
        /// Private helper function to fill out the Output struct by providing the object array.
        /// The object array is usually the result of SqlDataReader.GetValues()
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        private static TOutput CreateColumnOutput(SqlDataReader reader)
        {
            object[] columns = new object[ColumnCount];

            object ret = new TOutput();
            int i = 0;
            foreach (FieldInfo field in typeof(TOutput).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
            {
                ColumnMappingAttribute attr = Attribute.GetCustomAttribute(field, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
                if (attr != null)
                {
                    if (field.FieldType == typeof(SqlBytes))
                    {
                        columns[i] = reader.GetSqlBytes(i);
                    }
                    else if (field.FieldType == typeof(SqlChars))
                    {
                        columns[i] = reader.GetSqlChars(i);
                    }
                    else
                    {
                        columns[i] = reader.GetValue(i);
                    }

                    if (columns.Length <= i) throw new Exception("The number of parameters passed to CreateColumnOutput doesn't match with the number of columns mapped in Output struct");
                    typeof(TOutput).InvokeMember(
                        field.Name,
                        BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetField,
                        null, ret, new object[] { columns[i] == System.DBNull.Value ? null : columns[i] });
                    i++;
                }
            }

            if (columns.Length != i) throw new Exception("The number of parameters passed to CreateColumnOutput doesn't match with the number of columns mapped in Output struct");
            return (TOutput)ret;
        }

        /// <summary>
        /// Private helper functions to fill out the Output struct by providing the variable array
        /// The variable array store the return values of the stored procedure output parameters
        /// </summary>
        /// <param name="variables"></param>
        /// <returns></returns>
        protected static TOutput CreateVariableOutput(object[] variables)
        {
            object ret = new TOutput();
            int i = 0;
            foreach (FieldInfo field in typeof(TOutput).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
            {
                OutputMappingAttribute attr = Attribute.GetCustomAttribute(field, typeof(OutputMappingAttribute)) as OutputMappingAttribute;
                if (attr != null)
                {
                    if (variables.Length <= i) throw new Exception("The number of variables passed to CreateVariableOutput doesn't match with the number of variables mapped in Output struct");
                    object value = variables[i++];
                    if (value == DBNull.Value)
                        value = null;
                    typeof(TOutput).InvokeMember(
                        field.Name,
                        BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetField,
                        null, ret, new object[] { value });
                }
            }

            if (variables.Length != i) throw new Exception("The number of variables passed to CreateVariableOutput doesn't match with the number of variables mapped in Output struct");
            return (TOutput)ret;
        }

        /// <summary>
        /// Private helper function to create SqlCommand with correct timeout value
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        protected static SqlCommand CreateCommand(string sql)
        {
            return CreateCommand(null, sql);
        }

        /// <summary>
        /// Private helper function to create SqlCommand with correct timeout value
        /// </summary>
        /// <param name="connection">SQL connection</param>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        protected static SqlCommand CreateCommand(SqlConnection connection, string sql)
        {
            return SqlHelper.CreateCommand(connection, sql);
        }

        /// <summary>
        /// Private helper function to create the SqlCommand to call the stored procedure defined in this class
        /// and fill in the correct parameters using the provided input struct
        /// </summary>
        /// <param name="connection">SqlConnection used to connect to remote database</param>
        /// <param name="input">The input parameter for the internal stored proc</param>
        /// <returns></returns>
        protected static SqlCommand CreateCommand(SqlConnection connection, TInput input)
        {
            SqlCommand command = CreateCommand(connection, "");
            command.CommandType = CommandType.StoredProcedure;

            bool found = false;
            ParameterInfo[] spParams = null;
            foreach (MethodInfo method in typeof(T).GetMethods(BindingFlags.Static | BindingFlags.Public))
            {
                StoredProcedureAttribute procedure = Attribute.GetCustomAttribute(method, typeof(StoredProcedureAttribute)) as StoredProcedureAttribute;
                if (procedure != null)
                {
                    command.CommandText = typeof(T).Name;
                    connection.Trace(command.CommandText);
                    spParams = method.GetParameters();
                    found = true;
                    break;
                }
            }

            if (!found) throw new Exception("Unable to bind to the stored procedure implementation");

            SqlParameter[] parameters = Parameters(input);
            if (spParams.Length != parameters.Length) throw new Exception("Number of parameters defined in the stored procedure is not equal to the number of parameters defined in the Input class");

            // Convert the parameters name from Input struct field names to stored proc parameter names
            for (int i = 0; i < parameters.Length; i++)
            {
                parameters[i].ParameterName = "@" + spParams[i].Name;
                //because output parameter has no value before run the sql statement
                //we don't need to trace this in the moment
                if (parameters[i].Direction != ParameterDirection.Output)
                {
                    connection.Trace("{0} = {1}", parameters[i].ParameterName, ToSqlString(parameters[i].Value));
                }
            }

            command.Parameters.AddRange(parameters);
            return command;
        }

        protected static string ToSqlString(object data)
        {
            if (data == null) return "";

            if (data.GetType() == typeof(byte[]))
            {
                byte[] arr = (byte[])data;
                StringBuilder sqlString = new StringBuilder("0x");
                sqlString.Append(BitConverter.ToString(arr).Replace("-", ""));
                return sqlString.ToString();
            }

            return data.ToString();
        }
    }

    public static class SqlHelper
    {
        public static readonly Settings Settings = new Settings();
        public const string CONTEXT_CONNECTION = "context connection=true";

        /// <summary>
        /// Build the SQL statement by appending different elements in the object array
        /// The item inside the object array can be table, view, columns, variables or 
        /// static string
        /// </summary>
        /// <param name="values"></param>
        /// <returns></returns>
        public static string Build(params object[] values)
        {
            StringBuilder sql = new StringBuilder();
            foreach (object value in values)
            {
                // Table or view
                if (value is Type)
                {
                    string name = null;

                    TableAttribute table = Attribute.GetCustomAttribute((Type)value, typeof(TableAttribute)) as TableAttribute;
                    if (table != null) name = table.Name;

                    ViewAttribute view = Attribute.GetCustomAttribute((Type)value, typeof(ViewAttribute)) as ViewAttribute;
                    if (view != null) name = view.Name;

                    if (name == null) throw new Exception("The type should have either TableAttribute or ViewAttribute defined");
                    sql.Append(" [" + name + "] ");
                    continue;
                }

                // Normal static string
                if (value is string)
                {
                    sql.Append(" " + value + " ");
                    continue;
                }

                if (value is Enum)
                {
                    Type type = value.GetType();

                    // Variables
                    VariableAttribute variable = Attribute.GetCustomAttribute(type.GetField(value.ToString()), typeof(VariableAttribute)) as VariableAttribute;
                    if (variable != null)
                    {
                        sql.Append(" @" + value + " ");
                        continue;
                    }

                    // Full column name
                    ColumnAttribute column = Attribute.GetCustomAttribute(type.GetField(value.ToString()), typeof(ColumnAttribute)) as ColumnAttribute;
                    if (column != null)
                    {
                        string name = null;

                        TableAttribute table = Attribute.GetCustomAttribute((Type)type, typeof(TableAttribute)) as TableAttribute;
                        if (table != null) name = table.Name;

                        ViewAttribute view = Attribute.GetCustomAttribute((Type)type, typeof(ViewAttribute)) as ViewAttribute;
                        if (view != null) name = view.Name;

                        if (name != null) sql.Append(" [" + name + "].[" + value + "] ");
                        else sql.Append(" [" + value + "] ");
                        continue;
                    }

                    // Property name
                    PropertyAttribute property = Attribute.GetCustomAttribute(type.GetField(value.ToString()), typeof(PropertyAttribute)) as PropertyAttribute;
                    if (property != null)
                    {
                        sql.Append(" '" + value + "' ");
                        continue;
                    }

                    // Convert enum with various underlying type to corresponding numeric value
                    sql.Append(System.Convert.ChangeType(value, ((Enum)value).GetTypeCode()));
                    continue;
                }

                if (value is Guid)
                {
                    sql.Append(" '" + value.ToString() + "' ");
                    continue;
                }

                if (value is byte[])
                {
                    sql.Append(" " + BytesToHexString((byte[])value) + " ");
                    continue;
                }

                if (value is int)
                {
                    sql.Append(" " + value.ToString() + " ");
                    continue;
                }

                if (value is long)
                {
                    sql.Append(" " + value.ToString() + " ");
                    continue;
                }

                throw new Exception("Failed to build SQL statement"); // TODO: pass out the objects
            }

            return sql.ToString();
        }

        /// <summary>
        /// Compose the sql statement for CONVERT function
        /// toType can be SqlDataType or SystemPropertyItem enum
        /// expression can be variable
        /// </summary>
        /// <param name="toType"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static string Convert(object toType, object expression)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("CONVERT(");

            if (toType is Enum)
            {
                Type type = toType.GetType();

                // Property name
                PropertyAttribute property = Attribute.GetCustomAttribute(type.GetField(toType.ToString()), typeof(PropertyAttribute)) as PropertyAttribute;
                if (property != null)
                {
                    sql.Append(property.Type);

                    if (property.MaxLength.HasValue)
                    {
                        sql.AppendFormat("({0})", (property.MaxLength == -1) ? "MAX" : property.MaxLength.ToString());
                    }
                }
                else if (toType is SqlDbType)
                {
                    sql.Append(toType);
                }
                else
                {
                    throw new Exception("Failed to build SQL statement. Invalid type in Convert.");
                }
            }
            else
            {
                throw new Exception("Failed to build SQL statement. Invalid type in Convert.");
            }

            sql.Append(",");
            sql.Append(Build(expression));
            sql.Append(")");
            return sql.ToString();
        }

        /// <summary>
        /// Concatencate comma between each element of the item list.  The item inside the
        /// item lists can be table, view, columns, variables or static string
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        public static string List(params object[] items)
        {
            List<string> names = new List<string>();
            foreach (object item in items)
            {
                names.Add(Build(item));
            }

            return string.Join(", ", names.ToArray());
        }

        /// <summary>
        /// Convert byte array to hex string
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static string BytesToHexString(byte[] data)
        {
            StringBuilder sb = new StringBuilder("0x");

            foreach (byte aByte in data)
            {
                sb.Append(aByte.ToString("X2"));
            }

            return sb.ToString();
        }

        /// <summary>
        /// Gets the value of output parameter variable from the given command.
        /// </summary>
        /// <param name="command">Command.</param>
        /// <param name="variable">Variable name</param>
        /// <param name="value">Storing the value of the specified variable</param>
        /// <returns>The value of output parameter variable</returns>
        public static void GetValue<TValue>(this SqlCommand command, Enum variable, ref TValue value)
        {
            SqlParameter param = command.Parameters["@" + variable.ToString()];

            if (param.Value is TValue)
            {
                value = (TValue)param.Value;
            }
            else if (param.SqlValue is TValue)
            {
                value = (TValue)param.SqlValue;
            }

            command.Connection.Trace("{0} = {1}", "@" + variable.ToString(), value);
        }

        /// <summary>
        /// Defines the reference table column which property the SQL parameter definition should respect.
        /// </summary>
        /// <param name="parameters">The SQL parameters.</param>
        /// <param name="variable">The variable specifying the SQL parameter.</param>
        /// <param name="referenceColumn">The reference table column.</param>
        /// <returns>The SQL parameters.</returns>
        public static SqlParameter[] DefineReferenceTableColumn(this SqlParameter[] parameters, Enum variable, Enum referenceColumn)
        {
            if (parameters != null && variable != null && referenceColumn != null)
            {
                // Get the length definition of the reference table column
                FieldInfo columnFieldInfo = referenceColumn.GetType().GetField(referenceColumn.ToString());
                ColumnAttribute columnAttribute = Attribute.GetCustomAttribute(columnFieldInfo, typeof(ColumnAttribute), false) as ColumnAttribute;
                if (columnAttribute != null)
                {
                    int? referenceMaxLength = columnAttribute.MaxLength;
                    if (referenceMaxLength.HasValue && referenceMaxLength.Value > 0 && referenceMaxLength.Value != int.MaxValue)
                    {
                        // Define the length of the specified SQL parameter
                        string parameterName = "@" + variable.ToString();
                        foreach (SqlParameter parameter in parameters)
                        {
                            if (parameter.ParameterName.Equals(parameterName))
                            {
                                parameter.Size = referenceMaxLength.Value;
                                break;
                            }
                        }
                    }
                }
            }

            return parameters;
        }

        /// <summary>
        /// Private helper function to create SqlCommand with correct timeout value
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(string sql)
        {
            return CreateCommand(null, sql);
        }

        /// <summary>
        /// Private helper function to create SqlCommand with correct timeout value
        /// </summary>
        /// <param name="connection">SQL connection</param>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(SqlConnection connection, string sql)
        {
            SqlCommand command;
            if (connection == null)
            {
                command = new SqlCommand();
            }
            else
            {
                command = connection.CreateCommand();
            }

            command.CommandText = sql;

            // CONTEXT connection doesn't honor the timeout value anyway.
            //
            // If there is a long lasting operation on the stored proc, 
            // SQL Server won't cancel the on going operations even if the
            // client program reaches its timeout value.
            //
            // The only way to stop the on going operation is to disconnect
            // the SqlConnection. So, we always use infinite timeout inside 
            // stored proc because the client program can cancel the operations 
            // by disconnecting the SqlConnection when needed
            //
            command.CommandTimeout = Settings.CommandTimeout;
            return command;
        }

        public static void Trace(params string[] strings)
        {
        }

        /// <summary>
        /// Helper to manipulate multiple value string in stored procedure call and SQL building.
        /// </summary>
        public static class MulitpleValueString
        {
            private const string MultipleValueSeparator = ",";

            private static readonly string[] MultipleValueSeparatorArray = { MultipleValueSeparator };

            private static readonly string[] EmptyStringArray = new string[] { };

            /// <summary>
            /// Converts the multiple string values to single string input.
            /// </summary>
            /// <param name="values">The multiple string values.</param>
            /// <returns>The single string input.</returns>
            public static string ConvertToInput(IList<string> values)
            {
                if (values == null)
                {
                    return null;
                }
                else
                {
                    string[] array = new string[values.Count];
                    values.CopyTo(array, 0);
                    return string.Join(MultipleValueSeparator, array);
                }
            }

            /// <summary>
            /// Converts from single string input to multiple string values.
            /// </summary>
            /// <param name="value">The single string input.</param>
            /// <returns>The multiple string values.</returns>
            public static IList<string> ConvertFromInput(string value)
            {
                return string.IsNullOrEmpty(value) ? EmptyStringArray :
                    value.Split(MultipleValueSeparatorArray, StringSplitOptions.RemoveEmptyEntries);
            }

            public static string BuildWhereInClause(string format, string columnName, IList<string> values)
            {
                if (values != null && values.Count > 0)
                {
                    int valueCount = values.Count;
                    string[] parameterNames = new string[valueCount];
                    for (int i = 0; i < valueCount; i++)
                    {
                        parameterNames[i] = GetParameterName(columnName, i);
                    }

                    string whereInClause = string.Format("{0} IN ({1})", columnName, string.Join(",", parameterNames));
                    return string.Format(format, whereInClause);
                }
                else
                {
                    return string.Empty;
                }
            }

            public static void AddWhereInClauseParameters(SqlCommand command, string columnName, IList<string> values)
            {
                if (values != null && values.Count > 0)
                {
                    int valueCount = values.Count;
                    for (int i = 0; i < valueCount; i++)
                    {
                        command.Parameters.Add(new SqlParameter(GetParameterName(columnName, i), values[i]) { Direction = ParameterDirection.Input });
                    }
                }
            }

            private static string GetParameterName(string columnName, int index)
            {
                return string.Format("@{0}{1}", columnName, index);
            }

        }
    }

    public class Settings
    {
        public Action<string> TraceHandler { get; set; }
        public int CommandTimeout { get; set; }
    }
}
